package cn.edu.seu.cose.qol.model.dao.database.question;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.LinkedList;

import cn.edu.seu.cose.qol.model.dao.database.QolSchemaConnectionFactory;
import cn.edu.seu.cose.qol.model.po.question.Questionnaire;


public class QuestionnaireDataAccess {
	
	private static final String COLUMN_QUESTIONNAIRE_ID = "questionnaire_id";
	
	private static final String COLUMN_QUESTIONNAIRE_NAME =
			"name";
	
	private static final String STATEMENT_RETRIEVE_QUESTIONNAIRE_BY_ID =
			"SELECT * FROM qol_questionnaire WHERE questionnaire_id=?;";
	
	private static final String STATEMENT_RETRIEVE_QUESTIONNAIRE_BY_NAME =
			"SELECT * FROM qol_questionnaire WHERE name=?;";
	
	private static final String STATEMENT_RETRIEVE_QUESTIONNAIRES =
			"SELECT * FROM qol_questionnaire;";
	
	private static final String STATEMENT_CREATE_QUESTIONNAIRE =
			"INSERT INTO qol_questionnaire (name) VALUES (?);";
	
	private static final String STATEMENT_UPDATE_QUESTIONNAIRE =
			"UPDATE qol_questionnaire SET name=? WHERE questionnaire_id=?;";
	
	private static final String STATEMENT_DELETE_QUESTIONNAIRE_BY_ID =
			"DELETE FROM qol_questionnaire WHERE questionnaire_id=?;";
	
	private static final String STATEMENT_DELETE_QUESTIONNAIRE_BY_NAME =
			"DELETE FROM qol_questionnaire WHERE name=?;";
	
	private static QuestionnaireDataAccess _instance;
	
	private QolSchemaConnectionFactory _factory;
	
	public static QuestionnaireDataAccess getInstance() {
		if (_instance == null) {
			_instance = new QuestionnaireDataAccess();
		}
		return _instance;
	}
	
	private static Questionnaire getQuestionnaireByResultSet(ResultSet rs)
			throws SQLException {
		int questionnaireId = rs.getInt(COLUMN_QUESTIONNAIRE_ID);
		String name = rs.getString(COLUMN_QUESTIONNAIRE_NAME);
		
		Questionnaire questionnaire = new Questionnaire();
		questionnaire.setQuestionnaireId(questionnaireId);
		questionnaire.setName(name);
		return questionnaire;
	}
	
	protected QuestionnaireDataAccess() {
		_factory = QolSchemaConnectionFactory.getInstance();
	}
	
	public Questionnaire getQuestionnaireById(int id) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_RETRIEVE_QUESTIONNAIRE_BY_ID);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			
			if (!rs.next()) {
				return null;
			}
			
			return getQuestionnaireByResultSet(rs);
		} finally {
			connection.close();
		}
	}
	
	public Questionnaire getQuestionnaireByName(String name)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_RETRIEVE_QUESTIONNAIRE_BY_NAME);
			ps.setString(1, name);
			ResultSet rs = ps.executeQuery();
			
			if (!rs.next()) {
				return null;
			}
			
			return getQuestionnaireByResultSet(rs);
		} finally {
			connection.close();
		}
	}
	
	public Collection<Questionnaire> getQuestionnaires()
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_RETRIEVE_QUESTIONNAIRES);
			ResultSet rs = ps.executeQuery();
			
			LinkedList<Questionnaire> questionnaires =
					new LinkedList<Questionnaire>();
			while (rs.next()) {
				Questionnaire questionnaire = getQuestionnaireByResultSet(rs);
				questionnaires.add(questionnaire);
			}
			return questionnaires;
		} finally {
			connection.close();
		}
	}
	
	public int createNewQuestionnaire(Questionnaire questionnaire)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_CREATE_QUESTIONNAIRE, Statement.RETURN_GENERATED_KEYS);
			String name = questionnaire.getName();
			ps.setString(1, name);
			ps.executeUpdate();
			ResultSet rs = ps.getGeneratedKeys();
			
			if (!rs.next()) {
				return -1;
			}
			return rs.getInt(1);
		} finally {
			connection.close();
		}
	}
	
	public void updateQuestionnaire(Questionnaire questionnaire)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_UPDATE_QUESTIONNAIRE);
			int id = questionnaire.getQuestionnaireId();
			String name = questionnaire.getName();
			ps.setString(1, name);
			ps.setInt(2, id);
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}
	
	public void deleteQuestionnaireById(int id) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_DELETE_QUESTIONNAIRE_BY_ID);
			ps.setInt(1, id);
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}
	
	public void deleteQuestionnaireByName(String name)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_DELETE_QUESTIONNAIRE_BY_NAME);
			ps.setString(1, name);
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}

}
